#!/usr/bin/perl

use strict;
use warnings;
use DBI;


# MYSQL Server 1
my $host1 = '192.168.2.8';
my $database1 = 'logs';
my $mysqluser1 = 'logs';
my $mysqlpass1 = 'logs';
our ($dbh1, $sth1, $rv1, @data);

our $id;

$dbh1 = DBI->connect("DBI:mysql:$database1:$host1","$mysqluser1","$mysqlpass1", { RaiseError => 1, AutoCommit => 0 });

sub systeminfo
{
	my $adaptor = '0';
	my $adaptorcount = '0';
	my @Adaptors = `ifconfig`;
	my $ip = "";
	my $active = '0';
	my ($mac, $title);
	
	my $hostname = `hostname`;
	my $uname = `uname -a`;
	my $uptime = `uptime`;
	my $user = `id`;
	
	my @df = `df -T`;
	my ($dev, $filetype, $used, $available, $percent, $mountpoint);
	
	chomp($hostname);
	chomp($uname);
	chomp($uptime);
	chomp($user);
	
	#$uname =~ s/-|#//g;
	
	print "$hostname\n";
	print "$uname\n";
	print "$uptime\n";
	print "$user\n\n";
	
	$rv1  = $dbh1->do("INSERT INTO `Systems` (`Hostname`,`Uname`,`Uptime`,`User`) VALUES ('".$hostname."','".$uname."','".$uptime."','".$user."') ON DUPLICATE KEY UPDATE Hostname='".$hostname."', Uname='".$uname."', Uptime='".$uptime."', User='".$user."'");
	$id = $dbh1->last_insert_id(undef, undef, qw(a_table a_table_id));
	print "id $id\n";
	
	foreach my $line (@Adaptors)
	{
		if ($line =~ /(.*)Link encap:Ethernet .*HWaddr (.*)/i)
		{
			$title = $1;
			$mac = $2;
			$adaptor = '1';
		}
		
		if ($adaptor == 1)
		{
			if ($line =~ /inet addr:([^ ]+)/i)
			{
				$ip = $1;
				$rv1  = $dbh1->do("INSERT INTO `Adaptors` (`Sysid`, `Adaptor`, `Mac`,`Ip`,`Active`) VALUES ('".$id."','".$title."','".$mac."','".$ip."','1') ON DUPLICATE KEY UPDATE Adaptor='".$title."', Ip='".$ip."',Mac='".$mac."', Active='1'");
				$adaptor = '0';
			}
		}
	}
	
	foreach my $line (@df)
	{
		if ($line =~ /^\/([^ ]+)\s*([^ ]+)\s*([^ ]+)\s*([^ ]+)\s*([^ ]+)\s*([^ ]+)\s*([^ ]+)/i)
		{	
			$dev = $1;
			$filetype = $2;
			$used = $4;
			$available = $5;
			$percent = $6;
			$mountpoint = $7;
			$rv1  = $dbh1->do("INSERT INTO `Storage` (`Sysid`, `Dev`, `Filetype`, `Used`, `Available`, `Percent`,`MountPoint`) VALUES ('".$id."','".$dev."', '".$filetype."' ,'".$used."','".$available."','".$percent."','".$mountpoint."') ON DUPLICATE KEY UPDATE Sysid='".$id."', Dev='".$dev."', Filetype='".$filetype."', Used='".$used."', Available='".$available."', Percent='".$percent."', Mountpoint='".$mountpoint."'");
		}
	}
}

sub apache2
{
	
my ($sqldate, $time, $type, $client, $url, $Prot, $Status, $size, $referer, $Agent);
open (FILE, "/var/log/apache2/error.log");
$dbh1 = DBI->connect("DBI:mysql:$database1:$host1","$mysqluser1","$mysqlpass1", { RaiseError => 1, AutoCommit => 0 });

$sth1 = $dbh1->prepare("SELECT `time`, `client`, `url` FROM `Apache2-Access` ORDER BY `time` DESC LIMIT 1"); ## grab the last added log
$rv1 = $sth1->execute or die $sth1->errstr;  ## run above query
	while (@data = $sth1->fetchrow_array())  ## loop through each row
	{
		print "$data[0], $data[1], $data[2]\n\n";
		$sqldate = $data[0];
	}

while (<FILE>)
{
	$_ =~ s/('|"|\(|\)|\;|\-|\|)*//gi;
	#if ($_ =~ /[error] [client (.*)] File does not exist: (.*), referer:(.*)/i)
	if ($_ =~ /\[(.*)\]\s\[(error)\]\s\[client (.*)\] File does not exist: (.*), referer: (.*)/i)
	{
		my ($null, $month, $day, $time, $year) = split(/\s/, $1);
		$month = lookup($month);
		my $date = "$year-$month-$day $time";
		
		#print "Date: $date\n";
		#print "Type: $2\n";
		#print "Host: $3\n";
		#print "File: $4\n";
		#print "Ref: $5\n\n";
		#$rv1  = $dbh1->do("INSERT IGNORE INTO `Apache2-error` (`time`, `type`, `client`, `url`, `referer`) VALUES ('".$date."','".$2."','".$3."','".$4."','".$5."') WHERE `time` <> '".$date."' AND `url` <> '".$4."'";
	}
	if ($_ =~ /\[(.*)\]\s\[(notice)\]\s(.*)/i)
	{	
		my ($null, $month, $day, $time, $year) = split(/\s/, $1);
		
		$month = lookup($month);
		my $date = "$year-$month-$day $time";
		
		#print "date: $date\n";
		#print "Type: $2\n";
		#print "Notice: $3\n\n";
		
		#$rv1  = $dbh1->do("INSERT IGNORE INTO `Apache2-error` (`time`, `type`, `notice`) VALUES ('".$date."','".$2."','".$3."')");
	}
}

open (FILE, "/var/log/apache2/access.log");
$dbh1 = DBI->connect("DBI:mysql:$database1:$host1","$mysqluser1","$mysqlpass1", { RaiseError => 1, AutoCommit => 0 });
while (<FILE>)
{
	$_ =~ s/('|\(|\)|\;|\|)*//gi;
	
	if ($_ =~ /^([^ ]+) ([^ ]+) ([^ ]+) \[([^\/]+)\/([^\/]+)\/([^:]+):([^ ]+)([^\]]+)\] "([^ ]+) ([^ ]+) ([^"]+)" ([0-9]+) ([0-9]+) ([^ ]+) (.*)/i)
	{
		#print "client: $1\n";
		#print "day: $4\n";
		#print "month: $5\n";
		#print "year: $6\n";
		#print "time: $7\n";
		#print "24hr: $8\n";
		#print "type: $9\n";
		#print "url: $10\n";
		#print "prot: $11\n";
		#print "Status: $12\n";
		#print "size: $13\n";
		#print "referer: $14\n";
		#print "Agent: $15\n\n";
		
		$client = $1;
		$type = $9;
		$url = $10;
		$Prot = $11;
		$Status = $12;
		$size = $13;
		$referer = $14;
		$Agent = $15;
		
		my $month = lookup($5);
		my $date = "$6-$month-$4 $7";
		
		my ($tdate, $ttime) = split(/ /,$date);
		my ($sdate, $stime) = split(/ /,$sqldate);
		
		$tdate =~ s/-//g;
		$sdate =~ s/-//g;
		$ttime =~ s/://g;
		$stime =~ s/://g;
		
		if (($tdate == $sdate) & ($ttime > $stime) || ($tdate > $sdate))
		{
			print "Date same and Time bigger or time bigger\n";
			print "$sqldate\n";
			print "$date\n\n";
			
			$rv1  = $dbh1->do("INSERT IGNORE INTO `Apache2-Access` (`time`, `type`, `client`, `url`, `Prot`, `Status`, `something`, `referer`, `Agent`) VALUES ('".$date."', '".$type."', '".$client."', '".$url."', '".$Prot."', '".$Status."', '".$size."', '".$referer."', '".$Agent."')");
		}
		
		
	}
	}
}

sub lookup
{
	my $in = $_[0];
	my %lookup = (
					"Jan", "01",
					"Feb", "02",
					"Mar", "03",
					"Apr", "04",
					"May", "05",
					"Jun", "06",
					"Jul", "07",
					"Aug", "08",
					"Sep", "09",
					"Oct", "10",
					"Nov", "11",
					"Dec", "12"
				);
	my $rhash = \%lookup;
	unless (!$$rhash{$in}) {return $$rhash{$in}};
	return "NOCODE";
}

my @files = {'/var/logs/auth.log'};
my @commands;

open (FILE, "/var/log/auth.log");
while (<FILE>)
{
	if ($_ =~ /USER=(sp0tteh)/i)
	{
		if ($_ =~ /COMMAND=(.*)/)
		{
			push @commands, $1;
		}
	}
}

foreach (@commands)
{
	#print "$_\n";
}

#apache2;

systeminfo;
#print `uname -a`."\n";
#print `ifconfig`."\n";